﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;

namespace DBUtil.FastData;

/// <summary>
/// 操作数据的Api
/// </summary>
public class Api
{
    /// <summary>
    /// 构造函数
    /// </summary>
    public Api(DBAccess db)
    {
        this.db = db;
    }

    private DBAccess db { get; }

    #region 删除数据
    /// <summary>
    /// 删除数据
    /// </summary>
    /// <param name="commands">删除数据命令</param>
    /// <returns></returns>
    public CommandResult DeleteData(List<DeleteCommand> commands)
    {
        Dictionary<string, object> res = new Dictionary<string, object>();
        try
        {
            db.RunInTransaction(() =>
            {
                foreach (var command in commands)
                {
                    //关键字名称
                    string key = command.Tag;
                    string tbl = command.TableName;
                    //过滤条件
                    (string filterSql, DbParameter[] paras) = CreateFilter(command);
                    if (string.IsNullOrWhiteSpace(filterSql))
                    {
                        throw new Exception("删除的条件不能为空!");
                    }
                    int count = db.ExecuteSql($"delete from {tbl} {filterSql}", paras);
                    res.Add(key, new
                    {
                        successRes = true,
                        returnRes = "删除成功!",
                        countRes = count
                    });
                }
            });
            return new CommandResult()
            {
                Success = true,
                Data = res
            };
        }
        catch (Exception ex)
        {
            return new CommandResult()
            {
                Success = false,
                Message = ex?.Message
            };
        }
    }
    #endregion

    #region 查询数据
    /// <summary>
    /// 通用查询接口
    /// </summary>
    /// <param name="commands">查询命令</param>
    /// <returns></returns>
    public CommandResult QueryData(params QueryCommand[] commands)
    {
        var res = new CommandResult();
        foreach (var command in commands)
        {
            //关键字名称
            string key = command.Tag;
            string tbl = command.TableName;
            try
            {
                string sql = "";
                //过滤条件
                (string filterSql, IDataParameter[] paras) = CreateFilter(command);
                //排序字段
                string orderSql = "";
                if (command.Orders != null && command.Orders.Count > 0)
                {
                    foreach (var order in command.Orders)
                    {
                        string name = order.Name;
                        string type = order.IsDesc ? "desc" : "asc";
                        if (orderSql.Length > 0)
                        {
                            orderSql += string.Format(",{0} {1}", name, type);
                        }
                        else
                        {
                            orderSql += string.Format(" order by {0} {1}", name, type);
                        }
                    }
                }
                //请求字段
                string colSql = "*";
                Dictionary<string, string> formats = new Dictionary<string, string>();
                var _cols = command.Columns;
                if (_cols != null && _cols.Count > 0)
                {
                    foreach (var col in _cols)
                    {
                        var name = DealSqlFormat(col);
                        if (colSql == "*")
                        {
                            colSql = name;
                        }
                        else
                        {
                            colSql += "," + name;
                        }
                    }
                }
                var count = 0;
                if (command.PageSize > 0)
                {
                    //分页对象
                    count = db.SelectScalar<int>($"select count(1) from {tbl} {filterSql}");
                    int pageSize = command.PageSize;
                    int pageIndex = command.PageIndex;
                    sql = db.GetSqlForPageSize($"select {colSql} from {tbl} {filterSql}", orderSql, pageSize, pageIndex);
                }
                else
                {
                    sql = $"select {colSql} from {tbl} {filterSql} {orderSql}";
                }
                var dics = db.SelectDictionaryList(sql);
                count = Math.Max(count, dics.Count);
                //处理Format
                if (_cols != null && _cols.Count > 0)
                {
                    foreach (var dic in dics)
                    {
                        DealDisplayFormat(_cols, dic);
                    }
                }
                res.Data.Add(key, new
                {
                    successRes = true,
                    returnRes = dics,
                    countRes = count
                });
                res.Success = true;
                return res;
            }
            catch (Exception ex)
            {
                res.Data.Add(key, new
                {
                    successRes = false,
                    returnRes = ex?.Message
                });
            }
        }
        return res;
    }
    #endregion

    #region 拼接过滤sql
    /// <summary>
    /// 生成过滤条件sql
    /// </summary>
    /// <param name="filterCommand">命令</param>
    /// <returns></returns>
    private (string filterSql, DbParameter[] paras) CreateFilter(FilterCommand filterCommand)
    {
        var filterModels = filterCommand.Filters;
        var filterPrefix = filterCommand.Tag + "_";
        string sql = " where 1=1";
        List<DbParameter> paras = new List<DbParameter>();
        foreach (var filterModel in filterModels)
        {
            var name = filterModel.Name;
            var value = filterModel.Value;
            var type = filterModel.OperateType;
            var paraname = filterPrefix + name;
            var useCommonPara = true;
            object[] arr = null;
            switch (type)
            {
                case EnumOperateType.Equal:
                    sql += $" and {name} = {db.ParaPrefix}{paraname}";
                    break;
                case EnumOperateType.Greater:
                    sql += $" and {name} > {db.ParaPrefix}{paraname}";
                    break;
                case EnumOperateType.GreaterOrEqual:
                    sql += $" and {name} >= {db.ParaPrefix}{paraname}";
                    break;
                case EnumOperateType.Less:
                    sql += $" and {name} < {db.ParaPrefix}{paraname}";
                    break;
                case EnumOperateType.LessOrEqual:
                    sql += $" and {name} <= {db.ParaPrefix}{paraname}";
                    break;
                case EnumOperateType.Like:
                    sql += $" and {name} like {db.ParaPrefix}{paraname}";
                    useCommonPara = false;
                    paras.Add(db.CreatePara(paraname, $"%{value}%"));
                    break;
                case EnumOperateType.Like_Before:
                    sql += $" and {name} like {db.ParaPrefix}{paraname}";
                    useCommonPara = false;
                    paras.Add(db.CreatePara(paraname, $"{value}%"));
                    break;
                case EnumOperateType.Like_After:
                    sql += $" and {name} like {db.ParaPrefix}{paraname}";
                    useCommonPara = false;
                    paras.Add(db.CreatePara(paraname, $"%{value}"));
                    break;
                case EnumOperateType.NotEqual:
                    sql += $" and {name} <> {db.ParaPrefix}{paraname}";
                    break;
                case EnumOperateType.NotNull:
                    sql += $" and {name} is not null";
                    useCommonPara = false;
                    break;
                case EnumOperateType.IsNull:
                    sql += $" and {name} is null";
                    useCommonPara = false;
                    break;
                case EnumOperateType.IsNullOrEmpty:
                    sql += $" and ({name} is null or {name} = '')";
                    useCommonPara = false;
                    break;
                case EnumOperateType.NotNullOrEmpty:
                    sql += $" and {name} is not null and {name} <> ''";
                    useCommonPara = false;
                    break;
                case EnumOperateType.Between:
                    arr = value as object[];
                    sql += $" and {name} between {db.ParaPrefix}{paraname}_1 and {db.ParaPrefix}{paraname}_2";
                    useCommonPara = false;
                    paras.Add(db.CreatePara($"{paraname}_1", arr[0]));
                    paras.Add(db.CreatePara($"{paraname}_2", arr[1]));
                    break;
                case EnumOperateType.In:
                    arr = value as object[];
                    if (arr.Length == 0) break;
                    sql += $" and {name} in (";
                    var arr2 = new List<string>();
                    for (int i = 0; i < arr.Length; i++)
                    {
                        arr2.Add($"{db.ParaPrefix}{filterPrefix}{paraname}_{i + 1}");
                        paras.Add(db.CreatePara("{filterPrefix}{paraname}_{i + 1}", arr[i]));
                    }
                    sql += string.Join(",", arr2);
                    sql += ")";
                    useCommonPara = false;
                    break;
                case EnumOperateType.None:
                default:
                    break;
            }
            if (useCommonPara) paras.Add(db.CreatePara(paraname, value));
        }
        return (sql, paras.ToArray());
    }
    #endregion

    #region 处理Sql格式
    /// <summary>
    /// 处理Sql格式
    /// </summary>
    /// <param name="col"></param>
    /// <returns></returns>
    public string DealSqlFormat(ColumnRequest col)
    {
        var name = col.Name;
        switch (col.SqlFormat)
        {
            case EnumDataSqlFormat.Wkt:
                return $"{name}.STAsText() as {name}";
            case EnumDataSqlFormat.Count:
                return $"count(1) as {name}";
            case EnumDataSqlFormat.Avg:
                return $"avg({name}) as {name}";
            case EnumDataSqlFormat.Sum:
                return $"sum({name}) as {name}";
            case EnumDataSqlFormat.Max:
                return $"max({name}) as {name}";
            case EnumDataSqlFormat.Min:
                return $"min({name}) as {name}";
            default:
                return name;
        }

    }
    #endregion

    #region 处理显示的格式
    /// <summary>
    /// 处理显示的格式
    /// </summary>
    /// <param name="columnRequests"></param>
    /// <param name="dic"></param>
    public void DealDisplayFormat(List<ColumnRequest> columnRequests, IDictionary<string, object> dic)
    {
        var cols = dic.Keys.ToList();
        foreach (var item in cols)
        {
            var req = columnRequests.FirstOrDefault(c => c.Name == item);
            if (req != null)
            {
                switch (req.DisplayFormat)
                {
                    case EnumDataDisplayFormat.DateTimeLong:
                        if (DateTime.TryParse(dic[item].ToString(), out DateTime d))
                        {
                            dic[item] = d.ToString("yyyy-MM-dd HH:mm:ss.fff");
                        }
                        break;
                    case EnumDataDisplayFormat.DateTime:
                        if (DateTime.TryParse(dic[item].ToString(), out DateTime d2))
                        {
                            dic[item] = d2.ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        break;
                    case EnumDataDisplayFormat.Date:
                        if (DateTime.TryParse(dic[item].ToString(), out DateTime d3))
                        {
                            dic[item] = d3.ToString("yyyy-MM-dd");
                        }
                        break;
                    case EnumDataDisplayFormat.Time:
                        if (DateTime.TryParse(dic[item].ToString(), out DateTime d4))
                        {
                            dic[item] = d4.ToString("HH:mm:ss");
                        }
                        break;
                    case EnumDataDisplayFormat.Number:
                        {
                            if (double.TryParse(dic[item].ToString(), out double d5))
                            {
                                dic[item] = d5.ToString("0");
                            }
                            break;
                        }
                    case EnumDataDisplayFormat.Number_1:
                        {
                            if (double.TryParse(dic[item].ToString(), out double d5))
                            {
                                dic[item] = d5.ToString("0.0");
                            }
                            break;
                        }
                    case EnumDataDisplayFormat.Number_2:
                        {
                            if (double.TryParse(dic[item].ToString(), out double d5))
                            {
                                dic[item] = d5.ToString("0.00");
                            }
                            break;
                        }
                    case EnumDataDisplayFormat.Number_3:
                        {
                            if (double.TryParse(dic[item].ToString(), out double d5))
                            {
                                dic[item] = d5.ToString("0.000");
                            }
                            break;
                        }
                    case EnumDataDisplayFormat.Number_4:
                        {
                            if (double.TryParse(dic[item].ToString(), out double d5))
                            {
                                dic[item] = d5.ToString("0.0000");
                            }
                            break;
                        }
                    case EnumDataDisplayFormat.None:
                    default:
                        break;
                }
            }
        }
    }
    #endregion
}

/// <summary>
/// 命令执行结果
/// </summary>
public class CommandResult
{
    /// <summary>
    /// 是否成功
    /// </summary>
    public bool Success { get; set; }

    /// <summary>
    /// 错误描述
    /// </summary>
    public string Message { get; set; }

    /// <summary>
    /// 返回数据
    /// </summary>
    public Dictionary<string, object> Data { get; set; } = new Dictionary<string, object>();
}

/// <summary>
/// 抽象命令
/// </summary>
public abstract class Command
{
    /// <summary>
    /// 附带字符串数据
    /// </summary>
    public string Tag { get; set; }

    /// <summary>
    /// 操作的表名称
    /// </summary>
    public string TableName { get; set; }
}

/// <summary>
/// 带有过滤条件的命令
/// </summary>
public abstract class FilterCommand : Command
{
    /// <summary>
    /// 过滤条件
    /// </summary>
    public List<FilterModel> Filters { get; set; } = new List<FilterModel>();
}


/// <summary>
/// 删除命令
/// </summary>
public class DeleteCommand : FilterCommand { }

/// <summary>
/// 过滤条件
/// </summary>
public class FilterModel
{
    /// <summary>
    /// 列名称
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// 列值
    /// </summary>
    public object Value { get; set; }

    /// <summary>
    /// 过滤条件的操作类型
    /// </summary>
    public EnumOperateType OperateType { get; set; }
}

/// <summary>
/// 运算符
/// </summary>
public enum EnumOperateType
{
    /// <summary>
    /// 默认,不用处理
    /// </summary>
    None,

    /// <summary>
    /// 相等比较
    /// </summary>
    Equal,

    /// <summary>
    /// 大于
    /// </summary>
    Greater,

    /// <summary>
    /// 大于等于
    /// </summary>
    GreaterOrEqual,

    /// <summary>
    /// 小于
    /// </summary>
    Less,

    /// <summary>
    /// 小于等于
    /// </summary>
    LessOrEqual,

    /// <summary>
    /// 模糊匹配 like '%xxxx%'
    /// </summary>
    Like,

    /// <summary>
    /// 模糊匹配 like 'xxxx%'
    /// </summary>
    Like_Before,

    /// <summary>
    /// 模糊匹配 like '%xxxx'
    /// </summary>
    Like_After,

    /// <summary>
    /// 不等于
    /// </summary>
    NotEqual,

    /// <summary>
    /// 不为null
    /// </summary>
    NotNull,

    /// <summary>
    /// 为null
    /// </summary>
    IsNull,

    /// <summary>
    /// 为null或者为空字符串
    /// </summary>
    IsNullOrEmpty,

    /// <summary>
    /// 不为null,也不为空字符串
    /// </summary>
    NotNullOrEmpty,

    /// <summary>
    /// between...and...
    /// </summary>
    Between,

    /// <summary>
    /// in关键字
    /// </summary>
    In,
}

/// <summary>
/// 数据显示格式，由程序直接处理
/// </summary>
public enum EnumDataDisplayFormat
{
    /// <summary>
    /// 默认,不用处理
    /// </summary>
    None,

    /// <summary>
    /// 日期时间(含毫秒): yyyy-MM-dd HH:mm:ss.fff
    /// </summary>
    DateTimeLong,

    /// <summary>
    /// 日期时间: yyyy-MM-dd HH:mm:ss
    /// </summary>
    DateTime,

    /// <summary>
    /// 日期: yyyy-MM-dd
    /// </summary>
    Date,

    /// <summary>
    /// 时间: HH:mm:ss
    /// </summary>
    Time,

    /// <summary>
    /// 整形数据
    /// </summary>
    Number,

    /// <summary>
    /// 1位小数
    /// </summary>
    Number_1,

    /// <summary>
    /// 2位小数
    /// </summary>
    Number_2,

    /// <summary>
    /// 3位小数
    /// </summary>
    Number_3,

    /// <summary>
    /// 4位小数
    /// </summary>
    Number_4
}

/// <summary>
/// Sql操作格式，拼接到sql中执行
/// </summary>
public enum EnumDataSqlFormat
{
    /// <summary>
    /// 默认,不用处理
    /// </summary>
    None,

    /// <summary>
    /// 将空间数据转换为geojson格式: xxxx..STAsText() as xxxx
    /// </summary>
    Wkt,

    /// <summary>
    /// 计数: count(1) as xxxx
    /// </summary>
    Count,

    /// <summary>
    /// 平均值: avg(xxxx) as xxxx
    /// </summary>
    Avg,

    /// <summary>
    /// 合计值: sum(xxxx) as xxxx
    /// </summary>
    Sum,

    /// <summary>
    /// 最大值: max(xxxx) as xxxx
    /// </summary>
    Max,

    /// <summary>
    /// 最小值: min(xxxx) as xxxx
    /// </summary>
    Min
}

/// <summary>
/// 查询命令
/// </summary>
public class QueryCommand : FilterCommand
{
    /// <summary>
    /// 请求的列
    /// </summary>
    public List<ColumnRequest> Columns { get; set; }

    /// <summary>
    /// 排序请求列
    /// </summary>
    public List<OrderRequest> Orders { set; get; }

    /// <summary>
    /// 分页时当前的页码: 从1开始,默认为1
    /// </summary>
    public int PageIndex { get; set; } = 1;

    /// <summary>
    /// 分页时的页大小: 为0代表不分页,默认为0
    /// </summary>
    public int PageSize { get; set; }
}

/// <summary>
/// 排序列说明
/// </summary>
public class OrderRequest
{
    /// <summary>
    /// 排序的列名称
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// 是否反序: 默认是 asc
    /// </summary>
    public bool IsDesc { get; set; }
}

/// <summary>
/// 请求列
/// </summary>
public class ColumnRequest
{
    /// <summary>
    /// 列名称
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// 列格式: c#处理
    /// </summary>
    public EnumDataDisplayFormat DisplayFormat { get; set; }

    /// <summary>
    /// 列类型: 拼接到sql
    /// </summary>
    public EnumDataSqlFormat SqlFormat { get; set; }

    /// <summary>
    /// 其他过滤参数
    /// </summary>
    public object Parameter { get; set; }
}
